package bid.dao.impl;

import bid.dao.ProfessorDao;
import bid.entity.Professor;
import bid.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author oldliu
 * @since 1.0
 */
//mysql的实现
public class ProfessorDaoImpl implements ProfessorDao {
    @Override
    public int add(Professor u) {
        Connection con = DBUtil.get();
        PreparedStatement st = null;
        String sql = "insert into professor" +
                "(professorName,gender,birthday,identityNumber,workStatus,institution,education,major,employCompany,administrative,technicalPost,phone,mobliePhone,address,projectCategory,createBy,createDate,modifyBy,workDate,lostNum,modifyDate)";
        sql += "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try {
            st = con.prepareStatement(sql);//sql参数
            //填充占位符
            st.setString(1, u.getProfessorName());
            st.setString(2, u.getGender());
            st.setDate(3, u.getBirthday() == null ? null : new Date(u.getBirthday().getTime()));
            st.setString(4, u.getIdentityNumber());
            st.setString(5, u.getWorkStatus());
            st.setString(6, u.getInstitution());
            st.setString(7, u.getEducation());
            st.setString(8, u.getMajor());
            st.setString(9, u.getEmployCompany());
            st.setString(10, u.getAdministrative());
            st.setString(11, u.getTechnicalPost());
            st.setString(12, u.getPhone());
            st.setString(13, u.getMobliePhone());
            st.setString(14, u.getAddress());
            st.setString(15, u.getProjectCategory());
            st.setString(16, u.getCreateBy());
            st.setDate(17, u.getCreateDate() == null ? null : new Date(u.getCreateDate().getTime()));
            st.setString(18, u.getModifyBy());
            st.setInt(19, u.getWorkDate() );
            st.setLong(20, u.getLostNum());
            st.setDate(21, u.getModifyDate() == null ? null : new Date(u.getModifyDate().getTime()));

            int r = st.executeUpdate();
            return r;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(st, con);
        }
        return 0;
    }

    @Override
    public int update(Professor u) {
        Connection con = DBUtil.get();
        PreparedStatement st = null;
        String sql = "update Professor set  professorName=?,gender=?,birthday=?,identityNumber=?,workStatus=?,institution=?,education=?,major=?," +
                "employCompany=?,administrative=?,technicalPost=?,phone=?,mobliePhone=?,address=?,projectCategory=?," +
                "createBy=?,createDate=?,modifyBy=?,workDate=?,lostNum=?,modifyDate=? where id=?";
        try {
            st = con.prepareStatement(sql);//sql参数
            //填充占位符
            st.setString(1, u.getProfessorName());
            st.setString(2, u.getGender());
            st.setDate(3, u.getBirthday() == null ? null : new Date(u.getBirthday().getTime()));
            st.setString(4, u.getIdentityNumber());
            st.setString(5, u.getWorkStatus());
            st.setString(6, u.getInstitution());
            st.setString(7, u.getEducation());
            st.setString(8, u.getMajor());
            st.setString(9, u.getEmployCompany());
            st.setString(10, u.getAdministrative());
            st.setString(11, u.getTechnicalPost());
            st.setString(12, u.getPhone());
            st.setString(13, u.getMobliePhone());
            st.setString(14, u.getAddress());
            st.setString(15, u.getProjectCategory());
            st.setString(16, u.getCreateBy());
            st.setDate(17, u.getCreateDate() == null ? null : new Date(u.getCreateDate().getTime()));
            st.setString(18, u.getModifyBy());
            st.setInt(19, u.getWorkDate());
            st.setLong(20, u.getLostNum());
            st.setDate(21, u.getModifyDate() == null ? null : new Date(u.getModifyDate().getTime()));
            st.setLong(22, u.getId());
            int r = st.executeUpdate();
            return r;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(st, con);
        }
        return 0;
    }

    @Override
    public int del(long id) {
        Connection con = DBUtil.get();
        PreparedStatement st = null;
        String sql = "delete  from Professor where id=?";
        try {
            st = con.prepareStatement(sql);//sql参数
            //填充占位符
            st.setLong(1, id);
            int r = st.executeUpdate();
            return r;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(st, con);
        }
        return 0;
    }

    @Override
    public Professor get(long id) {
        String sql = "select * from Professor where id=?";

        Connection con = DBUtil.get();
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            st = con.prepareStatement(sql);//sql参数
            //填充占位符
            st.setLong(1, id);
            rs = st.executeQuery();
            if (rs.next()) {
                Professor professor = new Professor();
                professor.setId(rs.getLong("id"));
                professor.setProfessorName(rs.getString("professorName"));
                professor.setGender(rs.getString("gender"));
                professor.setBirthday(rs.getDate("birthday"));
                professor.setIdentityNumber(rs.getString("identityNumber"));
                professor.setWorkStatus(rs.getString("workStatus"));
                professor.setInstitution(rs.getString("institution"));
                professor.setEducation(rs.getString("education"));
                professor.setMajor(rs.getString("major"));
                professor.setEmployCompany(rs.getString("employCompany"));
                professor.setAdministrative(rs.getString("administrative"));
                professor.setTechnicalPost(rs.getString("technicalPost"));
                professor.setPhone(rs.getString("phone"));
                professor.setMobliePhone(rs.getString("mobliePhone"));
                professor.setAddress(rs.getString("address"));
                professor.setProjectCategory(rs.getString("projectCategory"));
                professor.setCreateBy(rs.getString("createBy"));
                professor.setCreateDate(rs.getDate("createDate"));
                professor.setModifyBy(rs.getString("modifyBy"));
                professor.setWorkDate(rs.getInt("workDate"));
                professor.setLostNum(rs.getLong("lostNum"));
                professor.setModifyDate(rs.getDate("modifyDate"));
                return professor;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(st, con);
        }
        return null;
    }

    @Override
    public List<Professor> search(int pageNo, int pageSize) {
        String sql = "select * from Professor  limit ?,?";
        List<Professor> professors = new ArrayList<>();
        Connection con = DBUtil.get();
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            st = con.prepareStatement(sql);//sql参数
            //填充占位符
            st.setLong(1, (pageNo - 1) * pageSize);
            st.setLong(2, pageSize);
            rs = st.executeQuery();
            while (rs.next()) {
                Professor professor = new Professor();
                professor.setId(rs.getLong("id"));
                professor.setProfessorName(rs.getString("professorName"));
                professor.setGender(rs.getString("gender"));
                professor.setBirthday(rs.getDate("birthday"));
                professor.setIdentityNumber(rs.getString("identityNumber"));
                professor.setWorkStatus(rs.getString("workStatus"));
                professor.setInstitution(rs.getString("institution"));
                professor.setEducation(rs.getString("education"));
                professor.setMajor(rs.getString("major"));
                professor.setEmployCompany(rs.getString("employCompany"));
                professor.setAdministrative(rs.getString("administrative"));
                professor.setTechnicalPost(rs.getString("technicalPost"));
                professor.setPhone(rs.getString("phone"));
                professor.setMobliePhone(rs.getString("mobliePhone"));
                professor.setAddress(rs.getString("address"));
                professor.setProjectCategory(rs.getString("projectCategory"));
                professor.setCreateBy(rs.getString("createBy"));
                professor.setCreateDate(rs.getDate("createDate"));
                professor.setModifyBy(rs.getString("modifyBy"));
                professor.setWorkDate(rs.getInt("workDate"));
                professor.setLostNum(rs.getLong("lostNum"));
                professor.setModifyDate(rs.getDate("modifyDate"));
                professors.add(professor);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, st, con);
        }
        return professors;
    }

    @Override
    public long searchTotal() {
        String sql = "select count(id) from Professor";

        Connection con = DBUtil.get();
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            st = con.prepareStatement(sql);//sql参数

            rs = st.executeQuery();
            if (rs.next()) {
                   return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, st, con);
        }
        return 0;
    }
}
